const connection = require('../app/database')
class UserService {
  async createUser(username, password) {
    const statement = `
      INSERT INTO user (username,password) VALUES (?,?);
    `
    const result = await connection.execute(statement, [username, password])
    return result
  }
  async getUserByUsername(username) {
    const statement = `
      SELECT * FROM user WHERE username = ? ;
    `
    const [result] = await connection.execute(statement, [username])
    return result
  }
  async getUserList(pagenum,pagesize) {
    const statement = `
      SELECT 	(SELECT COUNT(*) FROM user) totalCount,
				      JSON_ARRAYAGG(JSON_OBJECT('id',u.id,'username',u.username,'mobile',u.mobile,'email',u.email,'role_name',r.roleName)) users
      FROM	user u LEFT JOIN role r ON u.rid = r.id
      LIMIT ?,?;
    `
    const offset = (pagenum - 1) * pagesize
    const [result] = await connection.execute(statement, [offset+'', pagesize])
    return result[0]
   
  }
  async getUserById(id) {
    const statement = `
      SELECT id,username,rid role_id,mobile,email FROM user WHERE id = ?;
    `
    const [result] = await connection.execute(statement,[id])
    return result[0]
  }
  async updateUserById(id, email, mobile) {
  
    const statement = `
      UPDATE user SET email = ?,mobile = ? WHERE id = ?;
    `
    const [result] = await connection.execute(statement, [email,mobile,id])
    return result
  }
  async removeUserById(id) {
    const statement = `
      DELETE FROM user WHERE id = ?;
    `
    const [res] = await connection.execute(statement, [id])
    return res
  }
  async changeUserRole(id, rid) {
    const statement = `
      UPDATE user SET rid = ? WHERE id = ?;
    `
    const [result] = await connection.execute(statement,[rid,id])

    return result
  }
}
module.exports = new UserService()